import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="123456",
    database="runoob_db"
)
print(mydb)

myCursor = mydb.cursor()

#1 创建数据库
# myCursor.execute("CREATE DATABASE runoob_db")
#
# myCursor.execute("show databases")
#
# for x in myCursor:
#     print(x)


# 2 创建表
# myCursor.execute("CREATE TABLE sites (name varchar(255),url varchar(255))")
# myCursor.execute("SHOW TABLES")
# for x in myCursor:
#     print(x)

# 3 设置主键
# myCursor.execute("ALTER TABLE sites ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")

# 4 插入数据
# sql = "INSERT INTO sites (name,url)  values(%s,%s)"
# val = ("RUNOOB","https://www.runoob.com")
# myCursor.execute(sql,val)
# mydb.commit() #数据表内容有更新，必须使用到语句
# print(myCursor.rowcount,"记录插入成功。")

# 5 批量插入
# sql = "INSERT INTO sites (name,url) VALUES (%s,%s)"
# val =[
#     ('Google','https://www.google.com'),
#     ('Github','https://www.github.com'),
#     ('Taobao','https://www.taobao.com'),
#     ('stackoverflow','https://wwow.stackoverflow.com/')
# ]
#
# myCursor.executemany(sql,val)
# mydb.commit() # 数据表内容有更新，必须使用到该语句
# print(myCursor.rowcount,'记录插入成功。')
#
# sql = "INSERT INTO sites (name,url) VALUES (%s,%s)"
# val = ("Zhihu","https://www.zhihu.com")
# myCursor.execute(sql,val)
# mydb.commit()
# print("1 条记录已插入，ID:",myCursor.lastrowid)

# 6 查询数据
# myCursor.execute('SELECT * FROM sites')
# myresult = myCursor.fetchall() # fetchall() 获取所有记录
#
# for x in myresult:
#     print(x)

# myCursor.execute("SELECT * FROM sites")
# fetchone = myCursor.fetchone() # 获取一条数据
# print(fetchone)

# 6.2 为了防止数据查询发生SQL注入的攻击，我们可以使用%s占位符来转义查询的条件：
# sql = "SELECT * FROM sites WHERE name = %s "
# na = ("RUNOOB",)
# myCursor.execute(sql,na)
# fetchall = myCursor.fetchall()
# for x in fetchall:
#     print(x)

# 6.3 查询指定起始位置，使用关键字是OFFSET：
# myCursor.execute("SELECT * FROM sites LIMIT 1,3")
# fetchall = myCursor.fetchall()
# for x in fetchall:
#     print(x)

# 7 删除数据
# sql = "DELETE FROM sites WHERE name = 'stackoverflow'"
# myCursor.execute(sql)
# mydb.commit()
# print(myCursor.rowcount,"条记录删除")

# 7 更新数据表
# myCursor.execute("UPDATE sites set name ='ZH' WHERE name = 'Zhihu'")
# mydb.commit()
# print(myCursor.rowcount,"条记录被修改")

